package edu.lsnu.equipment_management_system.mapper.select.nopaging;

import edu.lsnu.equipment_management_system.pojo.po.EquipmentThreeLevelLinkagePo;
import edu.lsnu.equipment_management_system.pojo.po.UserTwoLevelLinkagePo;
import edu.lsnu.equipment_management_system.pojo.vo.out.chart.ChartVo;
import edu.lsnu.equipment_management_system.pojo.vo.out.drop_down_box.DropDownBoxVo;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author mzy
 * @Description 此mapper提供所有的联动，下拉框
 * @date 2018/11/16
 */
public interface CheckBoxMapper {

    /**
     * @Description 管理员层级：获取所有设备的三级联动
     * @author mzy
     * @time 2018-11-16
     * @see
     * @return   java.util.List<edu.lsnu.equipment_management_system.pojo.po.EquipmentThreeLevelLinkagePo>
     * @params  []
     */
    @Select("SELECT \n" +
            "\tDM_SBLB.SBLBDM 'equipmentCategoryNo',  \n" +
            "\tDM_SBLB.SBLB 'equipmentCategory', \n" +
            "\tJBXX_SB.SBMC 'equipmentName', \n" +
            "\tJBXX_SB.SBBH 'equipmentNo'\n" +
            "FROM DM_SBLB\n" +
            "inner join JBXX_SB\n" +
            "on DM_SBLB.SBLBDM = JBXX_SB.SBLBDM")
    List<EquipmentThreeLevelLinkagePo> getAdminEquipmentThreeLevelLinkagePo();


    /**
     * @Description 用户层级：获取和自己有关的设备的三级联动
     * @author mzy
     * @time 2018-11-16
     * @see
     * @return   java.util.List<edu.lsnu.equipment_management_system.pojo.po.EquipmentThreeLevelLinkagePo>
     * @params  []
     */
    @Select("SELECT \n" +
            "\tDM_SBLB.SBLBDM 'equipmentCategoryNo',  \n" +
            "\tDM_SBLB.SBLB 'equipmentCategory', \n" +
            "\tJBXX_SB.SBMC 'equipmentName', \n" +
            "\tJBXX_SB.SBBH 'equipmentNo'\n" +
            "FROM DM_SBLB\n" +
            "inner join JBXX_SB\n" +
            "on DM_SBLB.SBLBDM = JBXX_SB.SBLBDM\n" +
            "inner join \n" +
            "(\n" +
            "\tselect DISTINCT JBXX_SBSY.SBBH\n" +
            "\tfrom JBXX_SBSY\n" +
            "\twhere JBXX_SBSY.ZGH = #{userNo}\n" +
            "\tand JBXX_SBSY.SYRQ2 is null\n" +
            ") t\n" +
            "on JBXX_SB.SBBH = t.SBBH")
    List<EquipmentThreeLevelLinkagePo> getUserEquipmentThreeLevelLinkagePo(String userNo);


    /**
     * @Description 用户职工号的二级联动
     * @author mzy
     * @time 2018-11-16
     * @see
     * @return   java.util.List<edu.lsnu.equipment_management_system.pojo.po.UserTwoLevelLinkagePo>
     * @params  []
     */
    @Select("SELECT YHB.XM 'userName', YHB.ZGH 'userNo'\n" +
            "FROM YHB")
    List<UserTwoLevelLinkagePo> getUserTwoLevelLinkagePo();


    /**
     * @Description 获取所有的设备类别的下拉框
     * @author mzy
     * @time 2018-11-16
     * @see
     * @return   java.util.List<edu.lsnu.equipment_management_system.pojo.vo.out.drop_down_box.DropDownBoxVo>
     * @params  []
     */
    @Select("SELECT DM_SBLB.SBLBDM 'id', DM_SBLB.SBLB 'name' \n" +
            "FROM DM_SBLB ")
    List<DropDownBoxVo> getEquipmentCategoryDropDownBoxVo();


    /**
     * @Description 获取研究室下拉框
     * @author mzy
     * @time 2018-11-16
     * @see
     * @return   java.util.List<edu.lsnu.equipment_management_system.pojo.vo.out.drop_down_box.DropDownBoxVo>
     * @params  []
     */
    @Select("SELECT DM_YJS.YJSDM 'id', DM_YJS.YJSMC 'name'\n" +
            "FROM DM_YJS")
    List<DropDownBoxVo> getResearchRoomDropDownBoxVo();


    /**
     * @Description 获取存放场所的下拉菜单
     * @author mzy
     * @time 2018-11-16
     * @see
     * @return   java.util.List<edu.lsnu.equipment_management_system.pojo.vo.out.drop_down_box.DropDownBoxVo>
     * @params  []
     */
    @Select("SELECT DM_CFCS.CFCSDM 'id', DM_CFCS.CFCS 'name'\n" +
            "FROM DM_CFCS")
    List<DropDownBoxVo> getStoragePlaceDropDownBoxVo();

    /**
     * @Description 获取利用率模板的下拉菜单
     * @author mzywucai
     * @time 2019-01-15
     * @see 
     * @return   java.util.List<edu.lsnu.equipment_management_system.pojo.vo.out.drop_down_box.DropDownBoxVo>
     * @params  []
     */
    @Select("select\n" +
            "SBLYVBMDM 'id',\n" +
            "SBLYVBM 'name'\n" +
            "from DM_SBLYVBM")
    List<DropDownBoxVo> getUtilizationTemplateDropDownBoxVo();


    /**
     * @Description 获取普通用户首页柱状图
     * @author mzy
     * @time 2018-11-16
     * @see
     * @return   java.util.List<edu.lsnu.equipment_management_system.pojo.vo.out.chart.ChartVo>
     * @params  []
     */
    @Select("SELECT DM_SBLB.SBLB 'name', count(*) 'value'\n" +
            "FROM\n" +
            "DM_SBLB\n" +
            "inner join \n" +
            "JBXX_SB\n" +
            "on DM_SBLB.SBLBDM = JBXX_SB.SBLBDM\n" +
            "inner join \n" +
            "(\n" +
            "\tselect DISTINCT JBXX_SBSY.SBBH\n" +
            "\tfrom JBXX_SBSY\n" +
            "\twhere JBXX_SBSY.ZGH = #{userNo}\n" +
            "\tand JBXX_SBSY.SYRQ2 is null\n" +
            ") t\n" +
            "on JBXX_SB.SBBH = t.SBBH\n" +
            "group by DM_SBLB.SBLB")
    List<ChartVo> getUserHistogramChartVo(String userNo); // 50830025


    /**
     * @Description 管理员用户获取所有设备的柱状图
     * @author mzy
     * @time 2018-11-16
     * @see
     * @return   java.util.List<edu.lsnu.equipment_management_system.pojo.vo.out.chart.ChartVo>
     * @params  []
     */
    @Select("SELECT DM_SBLB.SBLB 'name', count(*) 'value'\n" +
            "FROM\n" +
            "DM_SBLB\n" +
            "inner join \n" +
            "JBXX_SB\n" +
            "on DM_SBLB.SBLBDM = JBXX_SB.SBLBDM\n" +
            "group by DM_SBLB.SBLB\n")
    List<ChartVo> getAdminHistogramChartVo();

    
    /**
     * @Description  获取普通用户首页饼状图
     * @author mzy
     * @time 2018-11-16
     * @see 
     * @return   java.util.List<edu.lsnu.equipment_management_system.pojo.vo.out.chart.ChartVo>
     * @params
     */
    @Select("SELECT DM_SBZT.SBZT 'name', count(*) 'value'\n" +
            "FROM\n" +
            "DM_SBZT\n" +
            "inner join \n" +
            "JBXX_SB\n" +
            "on DM_SBZT.SBZTDM = JBXX_SB.SBZTDM\n" +
            "inner join \n" +
            "(\n" +
            "\tselect DISTINCT JBXX_SBSY.SBBH\n" +
            "\tfrom JBXX_SBSY\n" +
            "\twhere JBXX_SBSY.ZGH = #{userNo}\n" +
            "\tand JBXX_SBSY.SYRQ2 is null\n" +
            ") t\n" +
            "on JBXX_SB.SBBH = t.SBBH\n" +
            "group by DM_SBZT.SBZT\n")
    List<ChartVo> getUserPieChartVo(String userNo); //50830030


    /**
     * @Description 管理员用户获取所有设备的饼状图
     * @author mzy
     * @time 2018-11-16
     * @see 
     * @return   java.util.List<edu.lsnu.equipment_management_system.pojo.vo.out.chart.ChartVo>
     * @params
     */
    @Select("SELECT DM_SBZT.SBZT 'name', count(*) 'value'\n" +
            "FROM\n" +
            "DM_SBZT\n" +
            "inner join \n" +
            "JBXX_SB\n" +
            "on DM_SBZT.SBZTDM = JBXX_SB.SBZTDM\n" +
            "group by DM_SBZT.SBZT")
    List<ChartVo> getAdminPieChartVo();
}
